from docutils.nodes import title
from flask import Flask, request, render_template
from datetime import datetime
from flask_sqlalchemy import SQLAlchemy
#pip install flask-migrate
from flask_migrate import Migrate

app = Flask(__name__)

HOSTNAME = "127.0.0.1"
PORT = "13308"
USERNAME = "root"
PASSWORD = "Vkgold20%40%40%40"  #@=%40
DATABASE = "flaskdemo"
db_url = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8mb4"
# print(db_url)
app.config['SQLALCHEMY_DATABASE_URI'] = db_url
db = SQLAlchemy(app)

migrate = Migrate(app, db)
#flask db init，初始化
#flask db migrate，识别ORM模型变化
#flask db upgrade，执行数据库升级操作

# with app.app_context():
#     with db.engine.connect() as conn:
#         rs = conn.execute(text("select * from user"))
#         print(rs.fetchone())
class UserDB(db.Model):
    __tablename__ = 'user'
    userid = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(255), unique=True, nullable=False)
    password = db.Column(db.String(255), nullable=False)

class ArticleDB(db.Model):
    __tablename__ = 'article'
    articleid = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(255), unique=True, nullable=False)
    content = db.Column(db.Text, nullable=False)

    author_id = db.Column(db.Integer, db.ForeignKey('user.userid'), nullable=False)
    #backref会自动给UserDB添加一个articles属性，用来获取文章列表
    author = db.relationship('UserDB', backref="articles")

#与migrate互斥
# with app.app_context():
#     db.create_all()

@app.route('/user/add', methods=['GET', 'POST'])
def user_add():
    user1 = UserDB(username="user1", userid=1)
    user2 = UserDB(username="user2", userid=2)
    db.session.add(user1)
    db.session.add(user2)
    db.session.commit()
    return "add succeed"

@app.route('/user/query', methods=['GET', 'POST'])
def user_query():
    # user1 = UserDB.query.get(1)
    # user1 = UserDB.query.first()
    # res = f"{user1.userid}:{user1.username}"

    # users = UserDB.query.filter(UserDB.username.like('%user%')).all()
    # users = UserDB.query.filter_by(userid=1)
    # # print(type(users))
    # for user in users:
    #     print(user.userid)

    #其他用法
    # UserDB.query.slice(start, stop)
    # UserDB.query.limit(limit)
    # UserDB.query.offset(offset)
    # UserDB.query.order_by(UserDB.userid).all()
    # UserDB.query.group_by(UserDB.userid).all()
    # in
    # UserDB.query.filter(UserDB.username.in_(["user1", "user2"])).all()
    # not in
    # UserDB.query.filter(~UserDB.username.in_(["user1", "user2"])).all()
    #is null
    # UserDB.query.filter(UserDB.username == None)
    # UserDB.query.filter(UserDB.username.is_(None))
    # is not null
    # UserDB.query.filter(UserDB.username != None)
    # UserDB.query.filter(UserDB.username.isnot(None))
    # from sqlalchemy import and_
    # users = UserDB.query.filter(and_(UserDB.username == "user1", UserDB.userid == 1)).all()
    # from sqlalchemy import or_
    # users = UserDB.query.filter(or_(UserDB.username == "user1", UserDB.username == "user2")).all()

    users = UserDB.query.all()
    for user in users:
        print(f"{user.userid}:{user.username}")

    return "query succeed"

@app.route('/user/update', methods=['GET', 'POST'])
def user_update():
    user = UserDB.query.filter_by(userid=1).first()
    user.username = "张三"
    db.session.commit()
    return "update succeed"
@app.route('/user/delete', methods=['GET', 'POST'])
def user_delete():
    user = UserDB.query.filter_by(userid=2).first()
    db.session.delete(user)
    db.session.commit()
    return "delete succeed"

@app.route('/article/add', methods=['GET', 'POST'])
def article_add():
    article1 = ArticleDB(title="黑神话·钟馗", content="从前有一个钟馗")
    article1.author = UserDB.query.filter_by(userid=1).first()
    article2 = ArticleDB(title="明末·渊虚之羽", content="从前有一个无常")
    article2.author = UserDB.query.filter_by(userid=1).first()
    db.session.add_all([article1, article2])
    db.session.commit()
    return "article add succeed"

@app.route('/article/query', methods=['GET', 'POST'])
def article_query():
    author = UserDB.query.filter_by(userid=1).first()
    for article in author.articles:
        print(f"{article.articleid}:{article.title}:{article.content}")
    return "article query succeed"

def datetime_format(value, format="%Y-%m-%d %H:%M:%S"):
    return value.strftime(format)

app.add_template_filter(datetime_format, "dformat")

class User:
    def __init__(self, name, age):
        self.name = name
        self.age = age

@app.route('/')
def index():
    user = User(name="zhangsan", age=777)
    mytime = datetime.now()
    person = {
        "name": user.name,
        "age": user.age
    }
    return render_template("index.html", user=user, person=person, mytime=mytime)

@app.route('/html')
def html():
    return render_template("hello.html")

@app.route('/profile')
def profile():
    return "个人中心"

@app.route('/channel/<int:channel_id>')
def channel(channel_id):
    return "您访问的是频道：%s" % channel_id

@app.route('/book/list')
def book_list():
    page = request.args.get('page', type=int, default=1)
    content = f"您获取的是图书列表第{page}页"
    return render_template("booklist.html", content=content)

@app.route('/control')
def control_demo():
    age = 18
    books = [{
        "name": "三国演义",
        "author": "罗贯中"
    },{
        "name": "西游记",
        "author": "吴承恩"
    }]
    return render_template("control.html", age=age, books=books)

@app.route('/child')
def child_demo():
    return render_template("child.html")

@app.route('/static')
def static_demo():
    return render_template("static.html")


if __name__ == '__main__':
    app.run()